package cn.ivicar.iov.service.impl;

import cn.ivicar.iov.dao.ExpendFlowInfoDao;
import cn.ivicar.iov.entity.BackInfo;
import cn.ivicar.iov.entity.ExpendFlowInfo;
import cn.ivicar.iov.util.DeCompressUtil;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;

/**
 * @Author: Chen
 * @Description:
 * @Date: Created in 11:42 2018/8/28
 */
@Service
public class ExcelToMysqlService {
    @Autowired
    private ExpendFlowInfoDao mapper;

    public BackInfo excelTomysql(MultipartFile fileZip, HttpServletRequest request) throws IOException {
        String zipName = null;
        if (fileZip != null && !fileZip.isEmpty()) {
            zipName = fileZip.getOriginalFilename();
        }
        System.out.println("文件夹名儿:" + zipName);
        String suffixStr = zipName.substring(zipName.lastIndexOf("."));
        System.out.println("后缀名儿:" + suffixStr);

        if (suffixStr.equals(".zip") || suffixStr.equals(".rar")) {
        } else {
            BackInfo backInfo = new BackInfo();
            backInfo.setMessage("请上传rar或zip的压缩文件");
            backInfo.setStatus(BackInfo.Status.ERROR);
            return backInfo;
        }


        //项目的绝对路径
        String realPath = request.getSession()
                .getServletContext()
                .getRealPath("upload");
        File file = new File(realPath);
        if (!file.exists()) {
            file.mkdir();
        }

        if (zipName != null && !"".equals(zipName.trim())) {
            String newName = "expendFlowInfo" + suffixStr;
            File desFile = new File(realPath + File.separator + newName);

            System.out.println("绝对路径: " + realPath);

            //该语句实现将原文件写到服务器中
            fileZip.transferTo(desFile);

            //项目根路径下的upload 下的文件名
            String zipUrl = request.getContextPath() + "/upload/" + newName;
            System.out.println("文件的路径: " + zipUrl);

            request.setAttribute("zipUrl", zipUrl);
            String sourceFilePath = realPath + "/" + newName;
            String destDirPath = realPath + "/" + "expendFlowInfoFile";
            System.out.println("真实文件路径: " + sourceFilePath);


            //开始解压缩文件并保存到upload文件夹里
            try {
                DeCompressUtil.deCompress(sourceFilePath, destDirPath);
                List<String> list = DeCompressUtil.getFiles(destDirPath);
                //查询upload文件夹里面的xls文件
//                Timer timer=new Timer();//实例化Timer类
//                timer.schedule(new TimerTask(){
//                    public void run(){
//                    }},5000);//五百毫秒
//                synchronized (this) {
//                    wait(5000);//等待1秒后，线程重新进入队列执行
//                }
                System.out.println("lalala" + list.get(0));
                xlsToMysql(list.get(0));


            } catch (Exception e) {
                request.setAttribute("error", "只支持zip和rar格式的压缩包！");
                BackInfo backInfo = new BackInfo();
                backInfo.setMessage("只支持zip和rar格式的压缩包！");
                backInfo.setStatus(BackInfo.Status.ERROR);
                e.printStackTrace();
                return backInfo;
            }

            BackInfo backInfo = new BackInfo();
            backInfo.setMessage("上传成功");
            backInfo.setStatus(BackInfo.Status.SUCCESS);

            return backInfo;
        }

        //写文件

        request.setAttribute("error","上传失败");
        BackInfo backInfo = new BackInfo();
        backInfo.setMessage("上传失败");
        backInfo.setStatus(BackInfo.Status.ERROR);

        return backInfo;
    }


    public void xlsToMysql(String destDirPath) throws ParseException {
        try {
//            String fileName = "/Users/wslp0314/Desktop/1.xlsx";
//            boolean isExcel2003 = getExtensionName(destDirPath) == "xls";

            int rows = 0;
            Workbook wookbook = null;
            Sheet sheet = null;
//            if (isExcel2003) {
//                wookbook = new HSSFWorkbook(new FileInputStream(destDirPath));
//            } else {
//                wookbook = new XSSFWorkbook(new FileInputStream(destDirPath));
//            }
//            boolean isExcel2003 = true;
//            if (getExtensionName(destDirPath).matches("^.+\\.(?i)(xlsx)$")) {
//                isExcel2003 = false;
//            }
//            if (isExcel2003) {
//                wookbook = new HSSFWorkbook(new FileInputStream(destDirPath));
//            } else {
//                wookbook = new XSSFWorkbook(new FileInputStream(destDirPath));
//            }

            try {
                wookbook = WorkbookFactory.create(new FileInputStream(destDirPath));
            } catch (InvalidFormatException i) {
                i.printStackTrace();
            }
//            sheet = wookbook.getSheet("Sheet1");
            sheet = wookbook.getSheetAt(0);
            rows = sheet.getPhysicalNumberOfRows();

            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            SimpleDateFormat simpleDateFormat1  =new SimpleDateFormat("yyyy-MM");
            //遍历行
            for (int i = 0; i <= sheet.getLastRowNum(); i++) {
                // 读取左上端单元格
                Row row = sheet.getRow(i);
                // 行不为空
                if (row != null && i != 0) {
                    //获取到Excel文件中的所有的列
                    int cells = row.getPhysicalNumberOfCells();
                    //遍历列
                    ExpendFlowInfo expendFlowInfo = new ExpendFlowInfo();
                    List l = null;
                    for (int j = 0; j < cells; j++) {
                        //获取到列的值
                        org.apache.poi.ss.usermodel.Cell cell = row.getCell(j);

                        String str = cell.getStringCellValue();
                        System.out.println(str);
                        //(#{seriesId},#{cardId},#{iccid},#{gprsStatus},#{setNum},#{flowSet},#{flowExpend},#{insertFlowPoolTime},#{expendTime})
                        if (j == 1) {
                            expendFlowInfo.setCardId(str);
                        } else if (j == 2) {
                            expendFlowInfo.setIccid(str);
                            l = mapper.findById(str);
                        } else if (j == 3) {
                            if (str.equals("正常")) {
                                expendFlowInfo.setGprsStatus(0);
                            } else if (str.equals("暂停")) {
                                expendFlowInfo.setGprsStatus(1);
                            }
                        } else if (j == 4) {
                            expendFlowInfo.setSetNum(str);
                        } else if (j == 5) {
                            expendFlowInfo.setFlowSet(str);
                        } else if (j == 6) {
                            expendFlowInfo.setFlowExpend(str);
                        } else if (j == 7 && str.length()>0) {
                            expendFlowInfo.setInsertFlowPoolTime(simpleDateFormat.parse(str));
                        } else if (j == 8) {
                            expendFlowInfo.setExpendTime(simpleDateFormat1.parse(str));
                        } else if (j==0){
                            expendFlowInfo.setSeriesId(str);
                        }
                    }
                    // 将数据插入到mysql数据库中
                    if (!l.isEmpty()) {
                        System.out.println("expendInfo存在");
                        mapper.update(expendFlowInfo);
                    } else {
                        System.out.println(expendFlowInfo);
                        mapper.save(expendFlowInfo);
                    }

                }
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
}
